# SQL 语句

# DDL

数据库定义语言，用来定义数据库对象：数据库，表，列等

操作数据库

-- DDL

-- 查询数据库
SHOW DATABASES ;

-- 创建数据库
CREATE DATABASE 数据库名称;
-- 判断数据库是否存在,不存在则创建
CREATE DATABASE IF NOT EXISTS 数据库名称;
CREATE DATABASE IF NOT EXISTS 数据库名称;

-- 删除数据库
DROP DATABASE 数据库名称;
-- 存在则删除
DROP DATABASE IF EXISTS 数据库名称;

-- 使用数据库
-- 查看当前使用的数据库
SELECT DATABASE();
-- 使用
USE 数据库名称;

操作数据表

-- 查询当前数据库下的所有表名称
SHOW TABLES ;
-- 查询表结构
DESC 表名称;

-- 创建表
CREATE TABLE 表名称(
    字段名 字段类型,
    字段名 字段类型,
    ....
    字段名 字段类型
    -- 最后一行没有逗号
);

-- 修改表
-- 1.修改表名
ALTER TABLE 表名称 RENAME TO 新的表名称;
-- 2.添加列
ALTER TABLE 表名称 ADD 列名 数据类型;
-- 3.修改数据类型
ALTER TABLE 表名称 MODIFY 要修改的列名 新的数据类型;
-- 4.修改列名和数据类型
ALTER TABLE 表名称 CHANGE 旧列名 新的列名 新的数据类型;
-- 5.删除列
ALTER TABLE 表名称 DROP 列名称;

-- 删除表
DROP TABLE 表名称;
DROP TABLE IF EXISTS 表名称;

# DML

数据操作语言，用来对数据表中的数据进行增删改查

添加数据

-- 给指定列添加数据
INSERT INTO students(列一, 列二, ...)
VALUES (值一，值二);

-- 给所有列添加数据
INSERT INTO students(全部列)
VALUES (全部值);

-- 批量添加数据
INSERT INTO students(列一，列二，列三....)
VALUES (值一，值二...),
       (值一，值二...),
       (值一，值二...);

修改和删除数据

-- 修改数据
UPDATE 表名
SET 列一 = 新值,
    列二 = 新值,
    ....
WHERE 筛选条件;


-- 删除数据
DELETE
FROM 表名
WHERE 筛选条件;

# DQL

数据查询语言，用来查询数据库中表的记录（数据）

SELECT 
	字段
FROM
	表
WHERE
	条件
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定

基础查询

-- 基础查询 ====================
-- 查询name,age 两列
SELECT name, age
FROM stu;
-- 查询所有列的数据，
SELECT *
FROM stu;
-- 去除重复记录
SELECT address
FROM stu;

SELECT DISTINCT address
FROM stu;
-- 起别名
SELECT math AS 数学, english AS 英语, address AS 地址
FROM stu;

条件查询（WHERE）

-- 条件查询 ============================
-- 1.查询年龄大于20岁的学员信息
SELECT name, age, sex, address, math, english, hire_date
FROM stu
WHERE age > 20;
-- 2.查询年龄大于等于20岁的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE age >= 20;
-- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30 岁的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE age >= 20
  AND age <= 30;

SELECT name, age, sex, address, math, english
FROM stu
WHERE age BETWEEN 20 AND 30;
-- 4.查询入学日期‘1998-09-01’到‘1999-09-01’之间的学员信息
SELECT name, age, sex, address, math, english, hire_date
FROM stu
WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';
-- 5.查询年龄等于18岁的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE age = 18;
-- 6.查询年龄不等于18岁的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE NOT age = 18;

SELECT name, age, sex, address, math, english
FROM stu
WHERE age != 18;

SELECT name, age, sex, address, math, english
FROM stu
WHERE age <> 18;
-- 7.查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE age = 18
   OR age = 20
   OR age = 22;

SELECT name, age, sex, address, math, english
FROM stu
WHERE age IN (18, 20, 22);
-- 8.查询英语成绩为NULL的学员信息
-- NULL 的比较不能使用 =  ！= ；需要使用 IS  IS NOT
SELECT name, age, sex, address, math, english
FROM stu
WHERE english IS NULL;

-- 模糊查询 ================
-- 1. 查询姓‘马’的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE name LIKE '马%';

-- 2.查询第二个字是‘花’的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE name LIKE '_花%';

-- 3.查询名字中包含‘德’的学员信息
SELECT name, age, sex, address, math, english
FROM stu
WHERE name LIKE '%德%';

排序查询（ORDER BY）

-- 排序查询 =====================
-- 1.查询学生信息，按照年龄升序排列
SELECT name, age, sex, address, math, english
FROM stu
ORDER BY age ASC;
-- 2.查询学生信息，按照数学成绩降序排列
SELECT name, age, sex, address, math, english
FROM stu
ORDER BY math DESC;
-- 3.查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩进行升序排列
SELECT name, age, sex, address, math, english
FROM stu
ORDER BY math DESC, english ASC;

分组查询（GROUP BY）

-- 聚合函数 =====================
-- NULL值统一不参与计算
-- 1.查询班级一共有多少个学生
-- 一般取值为主键或者*
SELECT COUNT(id)
FROM stu;
SELECT COUNT(*)
FROM stu;
-- 2.查询数学成绩的最高分
SELECT MAX(math)
FROM stu;
-- 3.查询数学成绩最低分
SELECT MIN(math)
FROM stu;
-- 4.查询数学成绩总分
SELECT SUM(math)
FROM stu;
-- 5.查询数学成绩的平均分
SELECT AVG(math)
FROM stu;
-- 6.查询英语成绩最低分
SELECT MIN(english)
FROM stu;

-- 分组函数 =================================
-- 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
-- 1.查询男同学和女同学各自的数学平均分
SELECT sex, AVG(math)
FROM stu
GROUP BY sex;
-- 2.查询男同学和女同学各自的数学平均分，以及各自人数
SELECT sex, AVG(math), COUNT(*)
FROM stu
GROUP BY sex;
-- 3.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70的不参与分组
SELECT sex, AVG(math), COUNT(*)
FROM stu
WHERE math >= 70
GROUP BY sex;
-- 4.查询男同学和女同学各自的数学平均分，以及各自人数，
-- 要求：分数低于70分的不参与分组，分组之后人数大于2的才能显示
SELECT sex, AVG(math), COUNT(*)
FROM stu
WHERE math >= 70
GROUP BY sex
HAVING COUNT(*) > 2;

分页查询

-- 分页查询 ============================
-- 起始索引从0开始
-- 1.从零开始查询，查询3条数据
SELECT name, age, sex, address, math, english, hire_date
FROM stu
LIMIT 0,3;
-- 2.每页显示3条数据，查询第一页数据
SELECT name,age,sex,address,math,english,hire_date
FROM stu
LIMIT 0,3;
-- 3.每页显示3条数据，查询第二页数据
SELECT name,age,sex,address,math,english,hire_date
FROM stu
LIMIT 3,3;
-- 4.每页显示3条数据，查询第三页数据
SELECT name,age,sex,address,math,english,hire_date
FROM stu
LIMIT 6,3;
-- 起始索引 = （当前页码-1）* 每页显示的条数

# DCL

数据控制语言，用来定义数据库的访问全新和安全级别，及创建用户

# 约束

-- 约束=============================
-- 创建员工表
CREATE TABLE emp
(
    id       INT PRIMARY KEY auto_increment, -- 员工ID,主键且自增长
    eName    VARCHAR(50)  NOT NULL UNIQUE,   -- 员工姓名，非空且唯一
    joinDate DATE         NOT NULL,          -- 入职日期，非空
    salary   DOUBLE(7, 2) NOT NULL,          -- 工资，非空
    bonus    DOUBLE(7, 2) DEFAULT 0          -- 奖金，如果没有奖金默认为0

);
-- 插入数据
INSERT INTO emp(ID, ENAME, JOINDATE, SALARY, BONUS)
VALUES (1, '张三', '2022-9-5', 9000, 5000);

-- 主键约束Demo:非空且唯一
INSERT INTO emp(ID, ENAME, JOINDATE, SALARY, BONUS)
VALUES (NULL, '张三', '2022-9-5', 9000, 5000);

INSERT INTO emp(ID, ENAME, JOINDATE, SALARY, BONUS)
VALUES (1, '张三', '2022-9-5', 9000, 5000);

-- 非空约束Demo
INSERT INTO emp(ID, ENAME, JOINDATE, SALARY, BONUS)
VALUES (2, NULL, '2022-9-5', 8000, 5000);

-- 唯一约束Demo
INSERT INTO emp(ID, ENAME, JOINDATE, SALARY, BONUS)
VALUES (2, '张三', '2022-9-5', 9000, 5000);

-- 默认约束Demo
INSERT INTO emp(ID, ENAME, JOINDATE, SALARY)
VALUES (2, '李四', '2022-9-5', 9000);


-- 外键约束 ------------------
DROP TABLE IF EXISTS emp;

-- 创建部门表
CREATE TABLE depart(
    id INT PRIMARY KEY AUTO_INCREMENT,
    depName VARCHAR(20),
    address VARCHAR(20)
);
-- 创建员工表
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    depID int,
    -- 添加外键 depID 关联 depart 表的id主键
    CONSTRAINT fk_emp_depart FOREIGN KEY (depID) REFERENCES depart(id)
);
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_depart;
-- 建完表后，添加外键
ALTER TABLE emp ADD  CONSTRAINT fk_emp_depart FOREIGN KEY (depID) REFERENCES depart(id);

# 多表关系

-- 多对多关系演示===================
-- 订单表
CREATE TABLE tb_order(
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment DOUBLE(10,2),
    payment_type TINYINT,
    status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    price DOUBLE(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    goods_id INT,
    count INT
);
-- 创建外键
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY (goods_id) REFERENCES tb_goods(id);

-- 一对一关系演示 ==================
-- 后创建
CREATE TABLE tb_user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    photo VARCHAR(64),
    nickname VARCHAR(20),
    age INT,
    gender CHAR(1),
    desc_id INT UNIQUE, -- 唯一外键
    CONSTRAINT fk_desc_id FOREIGN KEY (desc_id) REFERENCES tb_user_desc(id)
);

CREATE TABLE tb_user_desc(
    id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(10),
    edu VARCHAR(10),
    income DOUBLE(8,2)
);

# 多表查询

内连接

查询两张表交集的数据

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

-- 隐式内连接 ===================================
-- 查询emp的name,gender,dept表的dName
SELECT emp.name, emp.gender, dept.dName
FROM emp,
    dept
WHERE emp.dep_id = dept.did;
-- 给表起别名
SELECT t1.name, t1.gender, t2.dName
FROM emp t1,
    dept t2
WHERE t1.dep_id = t2.did;


-- 显示内连接
SELECT emp.id, emp.name, emp.gender, dept.did
FROM emp
        INNER JOIN dept ON emp.dep_id = dept.did;

SELECT emp.id, emp.name, emp.gender, dept.did
FROM emp
        JOIN dept ON emp.dep_id = dept.did;

外连接

查询一张表的全部数据和另一张表的交集部分

-- 左外连接
SELECT 字段列表
FROM 表一
         LEFT [OUTER] JOIN 表2 （别名） on 条件;

-- 右外连接
SELECT 字段列表
FROM 表一
         RIGHT [OUTER] JOIN 表2 （别名） on 条件;

-- 外连接====================================
-- 左外连接
SELECT emp.id, NAME, gender, salary, join_date, dep_id, d.dName
FROM emp
         LEFT OUTER JOIN dept d on d.did = emp.dep_id;

SELECT emp.id, NAME, gender, salary, join_date, dep_id, d.dName
FROM emp
         LEFT JOIN dept d on d.did = emp.dep_id;

-- 右外连接
SELECT emp.id, NAME, gender, salary, join_date, dep_id, d.dName
FROM emp
         RIGHT JOIN dept d on d.did = emp.dep_id;

子查询

-- 单行单列 作为条件值
SELECT 字段列表
FROM 表
WHERE 字段名 （= != < >） (子查询);

-- 多行单列 作为条件值
SELECT 字段列表
FROM 表
WHERE 字段名 in (子查询);

-- 多行多列 作为虚拟表
SELECT 字段列表
FROM （子查询）表名
WHERE 条件;

-- 子查询===========================
-- 查询比猪八戒工资高的员工的姓名和工资
SELECT salary
FROM emp
WHERE NAME = '猪八戒';

SELECT NAME, salary
FROM emp
WHERE salary > (SELECT salary FROM emp WHERE NAME = '猪八戒');


-- 查询‘财务部’和‘市场部’所有的员工信息
SELECT did
FROM dept
WHERE dName = '财务部'
   OR dName = '市场部';

SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID
FROM emp
WHERE dep_id in (SELECT did FROM dept WHERE dName = '财务部' OR dName = '市场部');


-- 查询入职日期是‘2011-11-11’之后的员工和部门信息
SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID
FROM emp
WHERE join_date > '2011-11-11';

SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID, DID, DNAME
FROM (SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID FROM emp WHERE join_date > '2011-11-11') t1,
     dept
WHERE dep_id = dept.did;

SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID, DID, DNAME
FROM (SELECT ID, NAME, GENDER, SALARY, JOIN_DATE, DEP_ID FROM emp WHERE join_date > '2011-11-11') t1
         JOIN
     dept
     ON dep_id = dept.did;

多表查询练习

-- 多表查询 练习 ===================================

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salaryGrade;


-- 部门表
CREATE TABLE dept
(
    id    INT PRIMARY KEY PRIMARY KEY, -- 部门id
    dName VARCHAR(50),                 -- 部门名称
    loc   VARCHAR(50)                  -- 部门所在地
);


-- 职务表，职务名称，职务描述
CREATE TABLE job
(
    id          INT PRIMARY KEY,
    jName       VARCHAR(20),
    description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp
(
    id       INT PRIMARY KEY, -- 员工id
    eName    VARCHAR(50),     -- 员工姓名
    job_id   INT,             -- 职务id
    mgr      INT,             -- 上级领导
    joinDate DATE,            -- 入职日期
    salary   DECIMAL(7, 2),   -- 工资
    bonus    DECIMAL(7, 2),   -- 奖金
    dept_id  INT,             -- 所在部门编号
    CONSTRAINT emp_jobId_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
    CONSTRAINT emp_deptId_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salaryGrade
(
    grade    INT PRIMARY KEY, -- 级别
    loSalary INT,             -- 最低工资
    hiSalary INT              -- 最高工资
);

-- 添加4个部门
INSERT INTO dept(id, dname, loc)
VALUES (10, '教研部', '北京'),
       (20, '学工部', '上海'),
       (30, '销售部', '广州'),
       (40, '财务部', '深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description)
VALUES (1, '董事长', '管理整个公司，接单'),
       (2, '经理', '管理部门员工'),
       (3, '销售员', '向客人推销产品'),
       (4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id, ename, job_id, mgr, joindate, salary, bonus, dept_id)
VALUES (1001, '孙悟空', 4, 1004, '2000-12-17', '8000.00', NULL, 20),
       (1002, '卢俊义', 3, 1006, '2001-02-20', '16000.00', '3000.00', 30),
       (1003, '林冲', 3, 1006, '2001-02-22', '12500.00', '5000.00', 30),
       (1004, '唐僧', 2, 1009, '2001-04-02', '29750.00', NULL, 20),
       (1005, '李逵', 4, 1006, '2001-09-28', '12500.00', '14000.00', 30),
       (1006, '宋江', 2, 1009, '2001-05-01', '28500.00', NULL, 30),
       (1007, '刘备', 2, 1009, '2001-09-01', '24500.00', NULL, 10),
       (1008, '猪八戒', 4, 1004, '2007-04-19', '30000.00', NULL, 20),
       (1009, '罗贯中', 1, NULL, '2001-11-17', '50000.00', NULL, 10),
       (1010, '吴用', 3, 1006, '2001-09-08', '15000.00', '0.00', 30),
       (1011, '沙僧', 4, 1004, '2007-05-23', '11000.00', NULL, 20),
       (1012, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30),
       (1013, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20),
       (1014, '关羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10);


-- 添加5个工资等级
INSERT INTO salaryGrade(grade, loSalary, hisalary)
VALUES (1, 7000, 12000),
       (2, 12010, 14000),
       (3, 14010, 20000),
       (4, 20010, 30000),
       (5, 30010, 99990);

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT emp.id      '员工编号',
       eName       '员工姓名',
       salary      '工资',
       jName       '职务名称',
       description '职务描述'
FROM emp
         RIGHT JOIN job j ON emp.job_id = j.id;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT emp.id 员工编号, eName 姓名, salary 工资, jName 职务, description 描述, dName 部门, loc 位置
FROM emp
         JOIN JavaWebDB2.job j on j.id = emp.job_id
         JOIN dept d on d.id = emp.dept_id;

-- 3.查询员工姓名，工资，工资等级
SELECT eName, salary, t.grade
FROM emp,
     salaryGrade t
WHERE salary BETWEEN t.loSalary AND t.hiSalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT eName 员工姓名, salary 工资, jName 职务名称, description 职务描述, dName 部门名称, loc 部门位置, grade 工资等级
FROM emp
         JOIN job j on emp.job_id = j.id
         JOIN dept d on emp.dept_id = d.id,
     salaryGrade t3
WHERE salary BETWEEN t3.loSalary AND t3.hiSalary;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT dept_id, COUNT(*)
FROM emp
GROUP BY dept_id;

SELECT d.id,dName,loc,e.count
FROM dept d,
     (SELECT dept_id, COUNT(*) count FROM emp GROUP BY dept_id) e
WHERE d.id =  e.dept_id;

事务
- 原子性（Atomicity）:事务是最小操作单位，要么同时成功，要么同时失败
- 一致性（Consistency）：事务完成时，必须使所有数据都保持一致状态
- 隔离性（Isolation）：多个事务之间，操作的可见性
- 持久性（Durability）：事务一旦提交或回滚，它对数据的改变就是永久的

-- 事务 ===================
CREATE TABLE account
(
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(20),
    money DOUBLE(10, 2)
);

INSERT INTO account(NAME, MONEY)
VALUES ('张三', 1000),
       ('李四', 1000);



SELECT name,money FROM account;
-- 转账事务 =================
-- 开启事务
BEGIN;
-- 判断李四余额


-- 李四金额 -500
UPDATE account
SET money = money - 500
WHERE name = '李四';

-- 出错！！！！

-- 张三余额 +500
UPDATE account
SET money = money + 500
WHERE name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK ;

← Java常用API 设计模式 →